package com.ruoyi.aitcommon.api;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONObject;
import com.ruoyi.aitcommon.anotation.ApiLog;
import com.ruoyi.aitcommon.config.ConfigManager;
import com.ruoyi.aitcommon.service.FlowService;
import com.ruoyi.aitcommon.service.SQLService;
import com.ruoyi.aitcommon.utils.ExcelUtils;
import com.ruoyi.aitcommon.utils.ExceptionUtils;
import com.ruoyi.aitcommon.utils.JSONResult;
import com.ruoyi.aitcommon.utils.SQLUtils;
import com.ruoyi.common.core.domain.AjaxResult;
import cn.hutool.core.util.StrUtil;

@RestController
@RequestMapping("/ait/common/sql")
public class SQLOperation {
	@Autowired
	FlowService flowService;
	@Autowired
	ConfigManager configManager;
	@Autowired
	SQLService sqlService;

	/**
     * 通过SQL语句,导出Excel数据
     * @param sql sql语句
     * @param db 数据库链接配置
     * @param path path条件
     * @param zjkName Excel页签名字
     * @return 
     * @exception 
     */
	@ApiLog("export")
    @GetMapping("/export")
    public AjaxResult excelExport(String sql, String db,String path,String zjkName)
    {
		try {
			String sqltext = (String)configManager.getContextByParm(sql);
			ExceptionUtils.checkBlankOrUndefined(sqltext, "参数[{}]获取SQL为空", sql);
			JSONObject dbjson = (JSONObject)configManager.getContextByParm(db);
			ExceptionUtils.checkBlankOrUndefined(dbjson, "参数[{}]获取DB链接为空", db);
			List<Map<String, Object>> retlist = sqlService.execQueryByPath(sqltext, dbjson, path);
	        String filename = ExcelUtils.exportExcel(retlist, zjkName);
	        return AjaxResult.success(filename);
		}catch(Exception ex) {
			return AjaxResult.error(ex.getMessage(), ex);
		}
		
    }
	
	/**
     * Excel导入更新DB数据
     * @param file 网页上传文件
     * @param db 数据库配置
     * @param table 更新表名
     * @param key 更新主键字段名
     * @return 更新数
     */
	@ApiLog("excelUpdate")
	@PostMapping("/excelUpdate")
    public AjaxResult excelUpdate(MultipartFile file, String db,String table,String key)
    {
		try {
//			通过Excel获取List Map 数据
			List<Map<String, Object>> datalist = ExcelUtils.importExcel(file.getInputStream(), null);
			JSONObject dbjson = (JSONObject)configManager.getContextByParm(db);
//			List Map数据更新到数据库
			int cnt = sqlService.mapUpdate(datalist, dbjson, table, key, null, null);
			String msg=String.format("Excel数据条数:[%s] <br>更新成功条数:[%s]",datalist.size(), cnt);
			return AjaxResult.success(msg);
		}catch(Exception ex) {
			return AjaxResult.error(ex.getMessage(), ex);
		}
		
    }
    
    /**
     * 分页查询数据
     * @param sql sql配置字符串
     * @param db db配置字符串
     * @param pageNum 页号
     * @param pageSize 分页大小
     * @param path 查询条件
     * @return
     */
	@ApiLog("queryByPage")
	@GetMapping("/queryByPage")
	public JSONObject queryByPage(String sql, String db
			, int pageNum, int pageSize,String path) {
		int total=0;
		try {
			if(StrUtil.isBlank(sql)||StrUtil.isBlank(db))
				return JSONResult.success();
			
			String sqltext = (String)configManager.getContextByParm(sql);
			ExceptionUtils.checkBlankOrUndefined(sqltext, "获取参数[{}]为空",sql);
			JSONObject dbjson = (JSONObject)configManager.getContextByParm(db);
			ExceptionUtils.checkBlankOrUndefined(dbjson, "获取参数[{}]为空",db);
			
//			获取记录数
			total=SQLUtils.getTotalCnt(sqltext, path, dbjson);
//			获取分页记录
			List<Map<String, Object>> ret=SQLUtils.execQueryPage(sqltext, path,"", dbjson, pageNum, pageSize);
			
//			构造返回结果
			Map<String,Object> retmap=new LinkedHashMap<String,Object>();
			retmap.put("total", total);
			retmap.put("rows", ret);
			return JSONResult.success(retmap);
		} catch (Exception ex) {
			return JSONResult.error(ex.getMessage(), ex);
		}
	}
}
